package com.gxuwz.leaveSystem.dao;

import com.gxuwz.leaveSystem.entity.Leave;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

import java.util.List;

public class LeaveDao {
    private JdbcTemplate jdbcTemplate;
    public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    /**
     * 添加
     * @param leave
     * @return 整数
     */

    public int addLeave(Leave leave) {
        String sql = "INSERT INTO `leave`.sys_leave (leaveID, courseId, reason, daynum, stuNo, applytime, status, audittime, opinion) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";
        return  jdbcTemplate.update(sql, leave.getLeaveID(), leave.getCourseID(), leave.getReason(), leave.getDaynum(), leave.getStuNo(), leave.getApplytime(), leave.getStatus(), leave.getAudittime(), leave.getOpinion());

    }

    /**
     * 修改
     * @param leave
     * @return 整数
     */
    public int updateLeave(Leave leave) {
        String sql = "UPDATE sys_leave SET courseId=?, reason=?, daynum=?, stuNo=?, applytime=?, status=?, audittime=?, opinion=? WHERE leaveID=?";
        return jdbcTemplate.update(sql, leave.getCourseID(), leave.getReason(), leave.getDaynum(), leave.getStuNo(), leave.getApplytime(), leave.getStatus(), leave.getAudittime(), leave.getOpinion(), leave.getLeaveID());
    }

    /**
     * 删除
     * @param leaveID
     * @return 整数
     */

    public int deleteLeave(String leaveID) {
        String sql = "DELETE FROM sys_leave WHERE leaveID=?";
        return jdbcTemplate.update(sql, leaveID);
    }

    /**
     * 根据id查询
     * @param leaveID
     * @return leave类型的记录
     */
    public Leave getLeaveById(String leaveID) {
        String sql = "SELECT * FROM sys_leave WHERE sys_leave.leaveID = ?";
        //通过rowmapper返回单行记录
        RowMapper<Leave> rowMapper = new BeanPropertyRowMapper<Leave>(Leave.class);
        // 使用query方法返回一个List
        List<Leave> leaves = this.jdbcTemplate.query(sql, rowMapper, leaveID);
        // 检查List是否为空
        if (leaves.isEmpty()) {
            return null; // 或者返回一个默认的User对象
        } else {
            return leaves.get(0);
        }
    }

    /**
     * 查询所有的记录
     * @return List<Leave>类型的记录
     */
    public List<Leave> getAllLeaves() {
        String sql = "SELECT * FROM sys_leave";
        RowMapper<Leave> rowMapper = new BeanPropertyRowMapper<>(Leave.class);
        return jdbcTemplate.query(sql, rowMapper);
    }

    /**
     * 根据关键字搜索
     * @param keyword
     * @return 列表
     */
    public List<Leave> search(String keyword) {
        String sql = "SELECT * FROM sys_leave WHERE sys_leave.leaveID LIKE ? OR sys_leave.courseID LIKE ? OR sys_leave.reason LIKE ? OR sys_leave.daynum LIKE ? OR sys_leave.stuNo LIKE ? OR sys_leave.applytime LIKE ? OR sys_leave.status LIKE ? OR sys_leave.audittime LIKE ? OR sys_leave.opinion LIKE ?";
        return jdbcTemplate.query(sql, new Object[]{"%" + keyword + "%","%" + keyword + "%","%" + keyword + "%","%" + keyword + "%","%" + keyword + "%","%" + keyword + "%","%" + keyword + "%","%" + keyword + "%", "%" + keyword + "%"}, new BeanPropertyRowMapper<>(Leave.class));
    }

}
